package org.example.musicproject.dao.impl;

import org.example.musicproject.dao.CommentAddsongsDao;
import org.example.musicproject.entity.CommentAddsongs;
import org.example.musicproject.util.DButil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

/**
 * 创建人:黄庭萍
 * 创建时间: 2024/12/10 20:46
 * 创建类型: CommentAddsongsDaoImpl
 */
public class CommentAddsongsDaoImpl implements CommentAddsongsDao {
    @Override
    public List<CommentAddsongs> all() {
        String sql = "SELECT  c.id AS cid,c.content,c.comment,\n" +
                "    COALESCE(c.delReason, '无') AS delReason,s.id AS soid,s.name AS soname,s.url\n" +
                "FROM comments c\n" +
                "LEFT JOIN commentAddsongs cas ON c.id = cas.cid\n" +
                "LEFT JOIN song s ON cas.soid = s.id;\n";
        System.out.println("=====查询所有歌曲评论关系绑定信息========");
        System.out.println("sql=" + sql);
        System.out.println("========================");
        return DButil.list(sql, CommentAddsongs.class);
    }

    @Override
    public CommentAddsongs byId(int id) {
        String sql = "SELECT  c.id AS cid, c.content, c.comment, " +
                "COALESCE(c.delReason, '无') AS delReason, s.id AS soid, s.name AS soname, s.url " +
                "FROM comments c " +
                "LEFT JOIN commentAddsongs cs ON c.id = cs.cid " +
                "LEFT JOIN song s ON cs.soid = s.id " +
                "WHERE c.id = ?";

        System.out.println("=====根据id歌曲评论关系绑定信息========");
        System.out.println("sql=" + sql);
        System.out.println("id=" + id);
        System.out.println("========================");

        return DButil.one(sql, CommentAddsongs.class, id);
    }

    @Override
    public int add(CommentAddsongs commentAddsongs) {
        String sql = "insert into commentAddsongs(soid,cid) values(?,?)";
        System.out.println("=====根据id添加歌曲评论关系绑定信息========");
        System.out.println("sql=" + sql);
        System.out.println("soid="+commentAddsongs.getSoid());
        System.out.println("cid="+commentAddsongs.getCid());
        System.out.println("========================");
        return DButil.update(sql,commentAddsongs.getSoid(), commentAddsongs.getCid());
    }

    @Override
    public int add(Connection conn, int cid, int soid) {
        String sql = "insert into commentAddsongs(soid,cid) values(?,?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, soid);
            pstmt.setInt(2, cid);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("数据库连接失败!原因:"+e.getMessage());
        }
//        System.out.println("=====根据id添加歌曲评论关系绑定信息========");
//        System.out.println("sql=" + sql);
//        System.out.println("conn=" + conn);
//        System.out.println("soid="+soid);
//        System.out.println("cid="+cid);
//        System.out.println("========================");
//        return DButil.update(sql,soid, cid, conn);
    }

    @Override
    public int update(CommentAddsongs commentAddsongs) {
        String sql = "update commentAddsongs set cid=?, soid=? where csoid=?";
        System.out.println("=====根据id修改歌曲评论关系绑定信息========");
        System.out.println("sql=" + sql);
        System.out.println("sql=" + sql);
        System.out.println("csid="+commentAddsongs.getCsoid());
        System.out.println("soid="+commentAddsongs.getSoid());
        System.out.println("cid="+commentAddsongs.getCid());
        System.out.println("========================");
        return DButil.update(sql,commentAddsongs.getCsoid(),commentAddsongs.getSoid(), commentAddsongs.getCid());
    }

    @Override
    public int delete(int cid,String delReason) {
        String sql = "UPDATE comments SET delReason = ? WHERE id = ?";
        Connection conn = DButil.getConn();
        try(PreparedStatement ps= conn.prepareStatement(sql)) {
            ps.setString(1, delReason);
            ps.setInt(2, cid);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("数据库连接失败!"+e.getMessage());
        }
        return cid;
    }

    @Override
    public int deleteBySoid(Connection conn, int soid) {
        String sql = "DELETE FROM commentAddsongs WHERE soid = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, soid);
            return pstmt.executeUpdate();
        }catch (SQLException ex){
            throw new RuntimeException("数据库连接失败!原因:"+ex.getMessage());
        }
    }

}
